﻿Imports System.Data.OleDb
Public Class frmThongKeNhapKho
    Private ReportType As Integer = 0
    Private IndexCurrentMonth As Integer = 0
    Private bEdit As Boolean
    Private Sub frmThongKeNhapKho_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Dim i As Integer
            For i = 0 To dgvThongKeNhap.ColumnCount - 1
                dgvThongKeNhap.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
                dgvThongKeNhap.ColumnHeadersHeight = 21
                dgvThongKeNhap.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter
            Next
            KMergeHeaderDGV1.Grid = dgvThongKeNhap
            DisableControl()
            dtpTheoNgay.Enabled = True
            Nam_Load()
            Thang_Load()
            Report_TheoNgay()
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub dgvThongKeNhap_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgvThongKeNhap.CellFormatting
        Try
            If e.ColumnIndex = 1 Then
                e.Value = Format(DateTime.Parse(e.Value.ToString), "dd/MM/yyyy")
                e.FormattingApplied = True
            End If
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgvThongKeNhap_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles dgvThongKeNhap.Paint
        KMergeHeaderDGV1.HeaderText = "Chứng từ xuất"
        KMergeHeaderDGV1.Merge(e, 0, 1)
        KMergeHeaderDGV1.HeaderText = "Chủng loại vật tư"
        KMergeHeaderDGV1.Merge(e, 2, 3)
        KMergeHeaderDGV1.HeaderText = FormatNumber(TongNhap, True)
        KMergeHeaderDGV1.Merge(e, 4, 4)
        KMergeHeaderDGV1.HeaderText = "Phần xuất vật tư"
        KMergeHeaderDGV1.Merge(e, 5, 5)
    End Sub
#Region "Thông kê nhập kho"
    Protected Function Create_NhapKho() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try
            Dim So As DataColumn = New DataColumn("So")
            So.DataType = System.Type.GetType("System.String")
            So.DefaultValue = ""
            dtTable.Columns.Add(So)

            Dim Ngay As DataColumn = New DataColumn("Ngay")
            Ngay.DataType = System.Type.GetType("System.String")
            Ngay.DefaultValue = ""
            dtTable.Columns.Add(Ngay)

            Dim MaSo As DataColumn = New DataColumn("MaSo")
            MaSo.DataType = System.Type.GetType("System.String")
            MaSo.DefaultValue = ""
            dtTable.Columns.Add(MaSo)

            Dim TenGoi As DataColumn = New DataColumn("TenGoi")
            TenGoi.DataType = System.Type.GetType("System.String")
            TenGoi.DefaultValue = ""
            dtTable.Columns.Add(TenGoi)

            Dim SoLuong As DataColumn = New DataColumn("SoLuong")
            SoLuong.DataType = System.Type.GetType("System.String")
            SoLuong.DefaultValue = ""
            dtTable.Columns.Add(SoLuong)

            Dim DienGiai As DataColumn = New DataColumn("DienGiai")
            DienGiai.DataType = System.Type.GetType("System.String")
            DienGiai.DefaultValue = ""
            dtTable.Columns.Add(DienGiai)
            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
    Private TongNhap As Single = 0
    Public Sub Report_TheoNgay()
        Try
            Dim TuNgay As Integer = ConvertDateToInt(dtpTheoNgay.Value.ToString.Split(" ")(0))
            Dim DenNgay As Integer = ConvertDateToInt(dtpTheoNgay.Value.ToString.Split(" ")(0))
            ThongKeNhapKho(TuNgay, DenNgay)
            'TongNhap = 0
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim dtIDSanPhamNhapKho As New DataTable
            'Dim query As String
            'query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuNhapKho' And Xoa=0 And TrangThai=N'Đã duyệt' And NgayNhapPhieu='" & dtpTheoNgay.Value.ToShortDateString & "'"
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtIDSanPhamNhapKho)
            'Dim dtNhapKho As DataTable = Create_NhapKho()
            'Dim SoLuong As Single = 0
            'For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
            '    query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
            '        " From PhieuNhapKho as P" & _
            '        " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
            '        " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
            '        " Where P.LoaiPhieu=N'PhieuNhapKho' And CT.LoaiPhieu=N'PhieuNhapKho' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NgayNhapPhieu='" & dtpTheoNgay.Value.ToShortDateString & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham") & " Order By P.So DESC"
            '    Dim dtSanPhamNhapKho As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtSanPhamNhapKho)
            '    dtSanPhamNhapKho.DefaultView.Sort = "Ngay ASC"
            '    If dtSanPhamNhapKho.Rows.Count > 0 Then
            '        Dim detailRow As DataRow
            '        For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
            '            detailRow = dtNhapKho.NewRow
            '            detailRow.Item("So") = dtSanPhamNhapKho.Rows(j)("So")
            '            detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(j)("Ngay")
            '            detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(j)("MaSanPham")
            '            detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(j)("TenSanPham")
            '            detailRow.Item("SoLuong") = dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu")
            '            detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(j)("NhapKhoTai")
            '            dtNhapKho.Rows.Add(detailRow)
            '        Next
            '    End If
            'Next
            'dbConn.Close()
            ''filter by code
            'Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            'If txtTenSanPham.Text <> "" Then
            '    For Each dr As DataRow In dtNhapKho.Rows
            '        'check 
            '        If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
            '            dtFilterByProductCode.ImportRow(dr)
            '        End If
            '    Next
            'Else
            '    dtFilterByProductCode = dtNhapKho
            'End If
            ''cap nhat tong nhap
            ''dtFilterByProductCode.Columns.Add("So", Type.GetType("System.Integer"))
            'For Each dr As DataRow In dtFilterByProductCode.Rows
            '    TongNhap = TongNhap + dr.Item("SoLuong")
            '    dr.Item("SoLuong") = FormatNumber(dr.Item("SoLuong"), False)
            '    dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            '    'dr.Item("So") = Integer.Parse(dr.Item("MaSo"))
            'Next
            'Dim data As New DataTable
            'Dim dv As DataView
            'dv = dtFilterByProductCode.DefaultView
            'dv.Sort = "Ngay DESC"
            'data = dv.ToTable()

            'dgvThongKeNhap.AutoGenerateColumns = False
            'dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoThang()
        Try
            Dim Thang As MonthInfo
            Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
            If Thang Is Nothing Then
                MessageBox.Show("Bạn chưa chọn tháng.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim Nam As YearInfo
            Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
            If Nam Is Nothing Then
                MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim TuNgay As Integer
            Dim DenNgay As Integer
            Dim sNgayCuoiThang As String
            Dim sThang As String
            Dim DaysInMonth As Integer = DateTime.DaysInMonth(Nam.TenNam, Thang.TenThang)
            If DaysInMonth <= 9 Then
                sNgayCuoiThang = "0" & DaysInMonth.ToString()
            Else
                sNgayCuoiThang = DaysInMonth.ToString()
            End If
            If Integer.Parse(Thang.TenThang) <= 9 Then
                sThang = "0" & Thang.TenThang
            Else
                sThang = Thang.TenThang
            End If
            TuNgay = Integer.Parse(Nam.TenNam & sThang & "01")
            DenNgay = Integer.Parse(Nam.TenNam & sThang & sNgayCuoiThang)
            ThongKeNhapKho(TuNgay, DenNgay)
            'TongNhap = 0
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim dtIDSanPhamNhapKho As New DataTable
            'Dim query As String
            'Dim Thang As MonthInfo
            'Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
            'If Thang Is Nothing Then
            '    MessageBox.Show("Bạn chưa chọn tháng.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            '    Exit Sub
            'End If
            'query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuNhapKho' And Xoa=0 And TrangThai=N'Đã duyệt' And ThangNhap='" & Thang.TenThang & "'"
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtIDSanPhamNhapKho)
            'Dim dtNhapKho As DataTable = Create_NhapKho()
            'Dim SoLuong As Single = 0
            'For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
            '    query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,P.NgayNhapBangSo,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
            '        " From PhieuNhapKho as P" & _
            '        " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
            '        " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
            '        " Where P.LoaiPhieu=N'PhieuNhapKho' And CT.LoaiPhieu=N'PhieuNhapKho' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.ThangNhap='" & Thang.TenThang & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham")
            '    Dim dtSanPhamNhapKho As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtSanPhamNhapKho)
            '    dtSanPhamNhapKho.DefaultView.Sort = "NgayNhapBangSo ASC"
            '    If dtSanPhamNhapKho.Rows.Count > 0 Then
            '        Dim detailRow As DataRow
            '        For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
            '            detailRow = dtNhapKho.NewRow
            '            detailRow.Item("So") = dtSanPhamNhapKho.Rows(j)("So")
            '            detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(j)("Ngay")
            '            detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(j)("MaSanPham")
            '            detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(j)("TenSanPham")
            '            detailRow.Item("SoLuong") = dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu")
            '            detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(j)("NhapKhoTai")
            '            dtNhapKho.Rows.Add(detailRow)
            '        Next
            '    End If
            'Next
            'dbConn.Close()
            ''filter by code
            'Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            'If txtTenSanPham.Text <> "" Then
            '    For Each dr As DataRow In dtNhapKho.Rows
            '        'check 
            '        If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
            '            dtFilterByProductCode.ImportRow(dr)
            '        End If
            '    Next
            'Else
            '    dtFilterByProductCode = dtNhapKho
            'End If
            ''cap nhat tong nhap
            'For Each dr As DataRow In dtFilterByProductCode.Rows
            '    TongNhap = TongNhap + dr.Item("SoLuong")
            '    dr.Item("SoLuong") = FormatNumber(dr.Item("SoLuong"), False)
            '    dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            'Next
            'Dim data As New DataTable
            'Dim dv As DataView
            'dv = dtFilterByProductCode.DefaultView
            'dv.Sort = "Ngay DESC, So DESC"
            'data = dv.ToTable()
            'dgvThongKeNhap.AutoGenerateColumns = False
            'dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoNam()
        Try
            Dim Nam As YearInfo
            Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
            If Nam Is Nothing Then
                MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            Dim TuNgay As Integer = Integer.Parse(Nam.TenNam & "01" & "01")
            Dim DenNgay As Integer = Integer.Parse(Nam.TenNam & "12" & "30")
            ThongKeNhapKho(TuNgay, DenNgay)
            'TongNhap = 0
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim dtIDSanPhamNhapKho As New DataTable
            'Dim query As String
            'Dim Nam As YearInfo
            'Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
            'If Nam Is Nothing Then
            '    MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
            '    Exit Sub
            'End If
            'query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuNhapKho' And Xoa=0 And TrangThai=N'Đã duyệt' And NamNhap='" & Nam.TenNam & "'"
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtIDSanPhamNhapKho)
            'Dim dtNhapKho As DataTable = Create_NhapKho()
            'Dim SoLuong As Single = 0
            'For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
            '    query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,P.NgayNhapBangSo,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
            '        " From PhieuNhapKho as P" & _
            '        " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
            '        " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
            '        " Where P.LoaiPhieu=N'PhieuNhapKho' And CT.LoaiPhieu=N'PhieuNhapKho' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NamNhap='" & Nam.TenNam & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham") & " Order By P.So DESC"
            '    Dim dtSanPhamNhapKho As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtSanPhamNhapKho)
            '    dtSanPhamNhapKho.DefaultView.Sort = "NgayNhapBangSo ASC"
            '    If dtSanPhamNhapKho.Rows.Count > 0 Then
            '        Dim detailRow As DataRow
            '        For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
            '            detailRow = dtNhapKho.NewRow
            '            detailRow.Item("So") = dtSanPhamNhapKho.Rows(j)("So")
            '            detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(j)("Ngay")
            '            detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(j)("MaSanPham")
            '            detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(j)("TenSanPham")
            '            detailRow.Item("SoLuong") = dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu")
            '            detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(j)("NhapKhoTai")
            '            dtNhapKho.Rows.Add(detailRow)
            '        Next
            '    End If
            'Next
            'dbConn.Close()
            ''filter by code
            'Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            'If txtTenSanPham.Text <> "" Then
            '    For Each dr As DataRow In dtNhapKho.Rows
            '        'check 
            '        If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
            '            dtFilterByProductCode.ImportRow(dr)
            '        End If
            '    Next
            'Else
            '    dtFilterByProductCode = dtNhapKho
            'End If
            ''cap nhat tong nhap
            'For Each dr As DataRow In dtFilterByProductCode.Rows
            '    TongNhap = TongNhap + dr.Item("SoLuong")
            '    dr.Item("SoLuong") = FormatNumber(dr.Item("SoLuong"), False)
            '    dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            'Next
            'Dim data As New DataTable
            'Dim dv As DataView
            'dv = dtFilterByProductCode.DefaultView
            'dv.Sort = "Ngay DESC"
            'data = dv.ToTable()

            'dgvThongKeNhap.AutoGenerateColumns = False
            'dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TuNgayDenNgay()
        Try
            Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
            Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
            If DenNgay < TuNgay Then
                Dim dlgRes As DialogResult
                dlgRes = MessageBox.Show( _
                          "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                          "Thông báo", _
                    MessageBoxButtons.OK, _
                          MessageBoxIcon.Question)
                If dlgRes = Windows.Forms.DialogResult.OK Then
                    Exit Sub
                End If
            End If
            ThongKeNhapKho(TuNgay, DenNgay)
            'TongNhap = 0
            'Dim dbConn As New OleDb.OleDbConnection
            'Dim dataAccess As New DataAccess
            'Dim dtIDSanPhamNhapKho As New DataTable
            'Dim query As String
            'query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuNhapKho' And Xoa=0 And TrangThai=N'Đã duyệt' And NgayNhapTheoSo <=" & DenNgay & " And NgayNhapTheoSo >=" & TuNgay & ""
            'Dim adapter As OleDb.OleDbDataAdapter
            'dataAccess.ConnectDatabase_DBAccess(dbConn)
            'adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            'adapter.Fill(dtIDSanPhamNhapKho)
            'Dim dtNhapKho As DataTable = Create_NhapKho()
            'Dim SoLuong As Single = 0
            'For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
            '    query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,P.NgayNhapBangSo,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
            '        " From PhieuNhapKho as P" & _
            '        " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
            '        " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
            '        " Where P.LoaiPhieu=N'PhieuNhapKho' And CT.LoaiPhieu=N'PhieuNhapKho' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NgayNhapTheoSo <=" & DenNgay & " And CT.NgayNhapTheoSo >=" & TuNgay & " And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham") & " Order By P.So DESC"
            '    Dim dtSanPhamNhapKho As New DataTable
            '    adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            '    adapter.Fill(dtSanPhamNhapKho)
            '    dtSanPhamNhapKho.DefaultView.Sort = "NgayNhapBangSo ASC"
            '    If dtSanPhamNhapKho.Rows.Count > 0 Then
            '        Dim detailRow As DataRow
            '        For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
            '            detailRow = dtNhapKho.NewRow
            '            detailRow.Item("So") = dtSanPhamNhapKho.Rows(j)("So")
            '            detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(j)("Ngay")
            '            detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(j)("MaSanPham")
            '            detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(j)("TenSanPham")
            '            detailRow.Item("SoLuong") = dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu")
            '            detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(j)("NhapKhoTai")
            '            dtNhapKho.Rows.Add(detailRow)
            '        Next
            '    End If
            'Next
            'dbConn.Close()
            ''filter by code
            'Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            'If txtTenSanPham.Text <> "" Then
            '    For Each dr As DataRow In dtNhapKho.Rows
            '        'check 
            '        If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
            '            dtFilterByProductCode.ImportRow(dr)
            '        End If
            '    Next
            'Else
            '    dtFilterByProductCode = dtNhapKho
            'End If
            ''cap nhat tong nhap
            'For Each dr As DataRow In dtFilterByProductCode.Rows
            '    TongNhap = TongNhap + dr.Item("SoLuong")
            '    'format number
            '    dr.Item("SoLuong") = FormatNumber(dr.Item("SoLuong"), False)
            '    dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            'Next
            'Dim data As New DataTable
            'Dim dv As DataView
            'dv = dtFilterByProductCode.DefaultView
            'dv.Sort = "Ngay DESC"
            'data = dv.ToTable()

            'dgvThongKeNhap.AutoGenerateColumns = False
            'dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
    End Sub
    Public Sub Report_TheoSoPhieu()
        Try

            TongNhap = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtIDSanPhamNhapKho As New DataTable
            Dim query As String
            query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuNhapKho' And Xoa=0 And TrangThai=N'Đã duyệt' And So=N'" & txtSoPhieu.Text.Trim & "'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtIDSanPhamNhapKho)
            Dim dtNhapKho As DataTable = Create_NhapKho()
            Dim SoLuong As Single = 0
            For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
                query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,P.NgayNhapBangSo,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
                    " From PhieuNhapKho as P" & _
                    " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
                    " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
                    " Where P.LoaiPhieu=N'PhieuNhapKho' And CT.LoaiPhieu=N'PhieuNhapKho' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.So=N'" & txtSoPhieu.Text.Trim & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham") & " Order By P.So DESC"
                Dim dtSanPhamNhapKho As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSanPhamNhapKho)
                dtSanPhamNhapKho.DefaultView.Sort = "NgayNhapBangSo ASC"
                If dtSanPhamNhapKho.Rows.Count > 0 Then
                    Dim detailRow As DataRow
                    For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
                        detailRow = dtNhapKho.NewRow
                        detailRow.Item("So") = dtSanPhamNhapKho.Rows(j)("So")
                        detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(j)("Ngay")
                        detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(j)("MaSanPham")
                        detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(j)("TenSanPham")
                        detailRow.Item("SoLuong") = dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu")
                        detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(j)("NhapKhoTai")
                        dtNhapKho.Rows.Add(detailRow)
                    Next
                End If
            Next
            dbConn.Close()
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtNhapKho.Rows
                    'check 
                    If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtNhapKho
            End If
            'cap nhat tong nhap
            For Each dr As DataRow In dtFilterByProductCode.Rows
                TongNhap = TongNhap + dr.Item("SoLuong")
                dr.Item("SoLuong") = FormatNumber(dr.Item("SoLuong"), False)
                dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            Next
            Dim data As New DataTable
            Dim dv As DataView
            dv = dtFilterByProductCode.DefaultView
            dv.Sort = "Ngay DESC"
            data = dv.ToTable()

            dgvThongKeNhap.AutoGenerateColumns = False
            dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
#Region "Thống Kê Nhập Kho"
    Private Sub ThongKeNhapKho(ByVal TuNgay As Integer, ByVal DenNgay As Integer)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dbCommand As OleDb.OleDbCommand
            Dim adapter As OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            Dim dtNhapKho As New DataTable
            dbCommand = New OleDbCommand("sp_ThongKeNhapKho", dbConn)
            dbCommand.CommandType = CommandType.StoredProcedure
            dbCommand.Parameters.Add("@MaSanPham", OleDbType.VarWChar).Value = txtTenSanPham.Text
            dbCommand.Parameters.Add("@TuNgay", OleDbType.VarChar).Value = TuNgay
            dbCommand.Parameters.Add("@DenNgay", OleDbType.VarChar).Value = DenNgay
            adapter = New OleDbDataAdapter(dbCommand)
            adapter.Fill(dtNhapKho)
            'TinhTongNhap
            dbCommand = New OleDbCommand("sp_ThongKeNhapKho_TongNhap", dbConn)
            dbCommand.CommandType = CommandType.StoredProcedure
            dbCommand.Parameters.Add("@MaSanPham", OleDbType.VarWChar).Value = txtTenSanPham.Text
            dbCommand.Parameters.Add("@TuNgay", OleDbType.VarChar).Value = TuNgay
            dbCommand.Parameters.Add("@DenNgay", OleDbType.VarChar).Value = DenNgay
            TongNhap = Single.Parse(dbCommand.ExecuteScalar())
            dbConn.Close()
            dgvThongKeNhap.AutoGenerateColumns = False
            dgvThongKeNhap.Columns(1).DefaultCellStyle.Format = "dd/MM/yyyy"
            dgvThongKeNhap.DataSource = dtNhapKho
        Catch ex As Exception
            ShowError(ex)
        End Try
    End Sub
#End Region
#Region "Filter Data"
    Sub DisableControl()
        dtpTheoNgay.Enabled = False
        cbxTheoThang.Enabled = False
        cbxNamTheoThang.Enabled = False
        cbxTheoNam.Enabled = False
        dtpTuNgay.Enabled = False
        dtpDenNgay.Enabled = False
        txtSoPhieu.Enabled = False
    End Sub

    Private Sub rbtTheoNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNgay.CheckedChanged
        DisableControl()
        dtpTheoNgay.Enabled = True
        ReportType = 0
    End Sub
    Private IsPostBack As Boolean = False
    Private Sub rbtTheoThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoThang.CheckedChanged
        DisableControl()
        cbxTheoThang.Enabled = True
        cbxNamTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxTheoThang.SelectedIndex = IndexCurrentMonth
        End If
        cbxNamTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxNamTheoThang.SelectedIndex = 0
        End If
        ReportType = 1
        IsPostBack = True
    End Sub
    Private IsPostBack2 As Boolean = False
    Private Sub rbtTheoNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNam.CheckedChanged
        DisableControl()
        cbxTheoNam.Enabled = True
        If IsPostBack2 = False Then
            cbxTheoNam.SelectedIndex = 0
        End If
        ReportType = 2
        IsPostBack2 = True
    End Sub

    Private Sub rbtTuNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTuNgay.CheckedChanged
        DisableControl()
        dtpTuNgay.Enabled = True
        dtpDenNgay.Enabled = True
        ReportType = 3
    End Sub
    Private Sub rbtTheoPhieu_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoPhieu.CheckedChanged
        Try
            DisableControl()
            txtSoPhieu.Enabled = True
            ReportType = 4
        Catch ex As Exception

        End Try
    End Sub
#End Region
#Region "Load combo tháng và combo năm"
#Region "Sub Class"
    Private Class YearInfo
        Dim _Nam As Integer
        Dim _TenNam As String
        Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
            _Nam = Nam
            _TenNam = TenNam
        End Sub
        Public Function Nam() As Integer
            Return _Nam
        End Function
        Public Function TenNam() As String
            Return _TenNam
        End Function
        Public Overrides Function ToString() As String
            Return _TenNam
        End Function
    End Class
    Private Class MonthInfo
        Dim _Thang As Integer
        Dim _TenThang As String
        Public Sub New(ByVal Thang As Integer, ByVal TenThang As String)
            _Thang = Thang
            _TenThang = TenThang
        End Sub
        Public Function Thang() As Integer
            Return _Thang
        End Function
        Public Function TenThang() As String
            Return _TenThang
        End Function
        Public Overrides Function ToString() As String
            Return _TenThang
        End Function
    End Class
#End Region
#Region "Load data"
    Private Sub Nam_Load()
        Dim currentYear As Integer = Now.Year
        cbxTheoNam.Items.Clear()
        Try
            While (currentYear >= 2011)
                cbxTheoNam.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                cbxNamTheoThang.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                currentYear = currentYear - 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Sub Thang_Load()
        Dim currentYear As Integer = 1
        cbxTheoThang.Items.Clear()
        Try
            Dim i As Integer = 0
            While (currentYear <= 12)
                cbxTheoThang.Items.Add(New MonthInfo(currentYear, currentYear.ToString))
                If currentYear = Now.Month Then
                    IndexCurrentMonth = i
                End If
                currentYear = currentYear + 1
                i = i + 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region
#End Region
    Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
        Try
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
                Case 4
                    Report_TheoSoPhieu()
            End Select
        Catch ex As Exception

        End Try
    End Sub

    Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
        Try
            Select Case ReportType
                Case 0
                    frmInThongKeNhapKho.ReportType = 0
                    frmInThongKeNhapKho.Ngay = dtpTheoNgay.Value.ToShortDateString
                    frmInThongKeNhapKho.dtNhapKho = dgvThongKeNhap.DataSource
                    frmInThongKeNhapKho.Show()
                Case 1
                    frmInThongKeNhapKho.ReportType = 1
                    Dim Thang As MonthInfo
                    Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                    frmInThongKeNhapKho.Thang = Thang.TenThang
                    frmInThongKeNhapKho.dtNhapKho = dgvThongKeNhap.DataSource
                    frmInThongKeNhapKho.Show()
                Case 2
                    frmInThongKeNhapKho.ReportType = 2
                    Dim Nam As YearInfo
                    Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                    frmInThongKeNhapKho.Nam = Nam.TenNam
                    frmInThongKeNhapKho.dtNhapKho = dgvThongKeNhap.DataSource
                    frmInThongKeNhapKho.Show()
                Case 3
                    frmInThongKeNhapKho.ReportType = 3
                    Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
                    Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
                    If DenNgay < TuNgay Then
                        Dim dlgRes As DialogResult
                        dlgRes = MessageBox.Show( _
                                  "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                                  "Thông báo", _
                            MessageBoxButtons.OK, _
                                  MessageBoxIcon.Question)
                        If dlgRes = Windows.Forms.DialogResult.OK Then
                            Exit Sub
                        End If
                    End If
                    frmInThongKeNhapKho.TuNgay = TuNgay
                    frmInThongKeNhapKho.DenNgay = DenNgay
                    frmInThongKeNhapKho.sTuNgay = Format(DateTime.Parse(dtpTuNgay.Value.ToShortDateString), "dd/MM/yyyy")
                    frmInThongKeNhapKho.sDenNgay = Format(DateTime.Parse(dtpDenNgay.Value.ToShortDateString), "dd/MM/yyyy")
                    frmInThongKeNhapKho.dtNhapKho = dgvThongKeNhap.DataSource
                    frmInThongKeNhapKho.Show()
                Case 4
                    frmInThongKeNhapKho.ReportType = 4
                    frmInThongKeNhapKho.SoPhieu = txtSoPhieu.Text
                    frmInThongKeNhapKho.dtNhapKho = dgvThongKeNhap.DataSource
                    frmInThongKeNhapKho.Show()
            End Select
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#Region "Tìm kiếm sản phẩm"
    Private Sub SanPham_Search(ByVal KeyWord As String)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String = "SELECT * FROM (Select SanPham.DVTSanPhamId,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa From SanPham " & _
                                    " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                    " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                    " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                    " Where SanPham.Xoa=0 And SanPham.MaSanPham Like N'" & KeyWord & "%') AS SP WHERE SP.DVTSANPHAMID IS NOT NULL Order By TenSanPham"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham_Search.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                'chú giải
            Next

            dgvSanPham_Search.DataSource = dtSanPham
            If dgvSanPham_Search.Rows.Count - 1 > 0 Then
                dgvSanPham_Search.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTenSanPham_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                Dim TenSanPham As String
                Dim DVT As String
                TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                txtTenSanPham.Text = TenSanPham
                'hide search panel
                PanelSanPham_Search.Visible = False
                cmdThongKe.Focus()
            End If
        Catch ex As Exception

        End Try
    End Sub



    Private Sub txtTenSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyUp
        Try
            If txtTenSanPham.Text <> "" Then
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgvSanPham_Search.Focus()
                End If
            End If
            txtTenSanPham.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgvSanPham_Search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvSanPham_Search.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    PanelSanPham_Search.Visible = True
                Case Keys.Up
                    PanelSanPham_Search.Visible = True
                Case Keys.Enter
                    Dim TenSanPham As String
                    Dim DVT As String
                    TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                    DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                    txtTenSanPham.Text = TenSanPham
                    'hide search panel
                    PanelSanPham_Search.Visible = False
                    cmdThongKe.Focus()
                Case Else
                    txtTenSanPham.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtTenSanPham_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenSanPham.TextChanged
        Try
            If txtTenSanPham.Text <> "" Then
                'show search panel
                If Not bEdit Then
                    PanelSanPham_Search.Visible = True
                    SanPham_Search(txtTenSanPham.Text)
                Else
                    PanelSanPham_Search.Visible = False
                End If
            Else
                'hide search panel
                PanelSanPham_Search.Visible = False
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvSanPham_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham_Search.CellDoubleClick
        Try
            'Gan Du Lieu
            Dim TenSanPham As String
            Dim DVT As String
            TenSanPham = dgvSanPham_Search.Rows(e.RowIndex).Cells("MaSanPham").Value.ToString
            DVT = dgvSanPham_Search.Rows(e.RowIndex).Cells("TenDonViTinh").Value.ToString
            txtTenSanPham.Text = TenSanPham
            'hide search panel
            PanelSanPham_Search.Visible = False
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
#Region "Export Excel"
    Private Sub cmdConvertExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdConvertExcel.Click
        Try
            frmSaveFile.FileType = ".xls"
            ShowModalForm(frmSaveFile, False, False)
            Dim result As Boolean
            result = frmSaveFile.Result
            If result Then
                Dim Path As String = frmSaveFile.Path
                Dim FileName As String = frmSaveFile.FileName
                ExportExcel(Path, FileName & ".xls")
            End If
            frmSaveFile.txtPath.Text = ""
            frmSaveFile.txtFile.Text = ""
        Catch ex As Exception

        End Try
    End Sub
    Private Sub ExportExcel(ByVal Path As String, ByVal FileName As String)
        Try
            Dim SubTitle As String
            If rbtTheoNgay.Checked Then
                SubTitle = "Ngày " & formatDateVN2String(dtpTheoNgay.Value)
            ElseIf rbtTheoThang.Checked Then
                Dim Thang As MonthInfo
                Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                Dim Nam As YearInfo
                Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
                SubTitle = " Tháng " & Thang.TenThang & " Năm " & Nam.TenNam
            ElseIf rbtTheoNam.Checked Then
                Dim Nam As YearInfo
                Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                SubTitle = " Năm " & Nam.TenNam
            ElseIf rbtTuNgay.Checked Then
                SubTitle = " Từ ngày " & formatDateVN2String(dtpTuNgay.Value.ToString.Split(" ")(0)) & " Đến ngày " & formatDateVN2String(dtpDenNgay.Value.ToString.Split(" ")(0))
            End If
            'write
            'read from template
            Dim CurrentDir As String = System.IO.Path.GetDirectoryName(Application.ExecutablePath)
            Dim PathTemPlate As String
            PathTemPlate = CurrentDir + "\TemplateExcel\ThongKeNhapKho_Header.txt"
            Dim Header As String = String.Format(System.IO.File.ReadAllText(PathTemPlate), SubTitle, Format(TongNhap, "N02"))
            ExportToFile(Path, FileName, Header)
            'content
            Dim contentExcelXML As String = ""
            'first row
            If dgvThongKeNhap.Rows.Count > 0 Then
                contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                       "<Cell ss:Index=""" & "3""" & "><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(0, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(1, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(2, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(3, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(4, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(5, 0).Value & "</Data></Cell>" & vbCrLf & _
                   "</Row>"
                ExportToFile(Path, FileName, contentExcelXML)
            End If
            'contentExcelXML = DevideRowGrid()
            Dim EndRow As Integer = dgvThongKeNhap.Rows.Count - 1
            Dim StartRow As Integer = 0
            Dim K As Integer = EndRow \ 100
            Dim SoDu As Integer = EndRow Mod 100
            Dim aK(100) As Integer
            For i As Integer = 1 To 100
                If K * i <= EndRow Then
                    aK(i) = K * i
                End If
            Next
            aK(100) = 100 * K + SoDu
            Dim sRet As String = ""
            frmBusyLoading.Show()
            frmBusyLoading.progress.Minimum = 0
            frmBusyLoading.progress.Maximum = 100
            For i As Integer = 0 To 99
                sRet = ConvertExcel(aK(i) + 1, aK(i + 1))
                If sRet <> "" Then
                    ExportToFile(Path, FileName, sRet)
                End If
                frmBusyLoading.progress.Value = i
            Next
            frmBusyLoading.Close()
            'end content
            PathTemPlate = CurrentDir + "\TemplateExcel\ThongKeNhapKho_End.txt"
            Dim Footer As String = System.IO.File.ReadAllText(PathTemPlate)
            ExportToFile(Path, FileName, Footer)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Function ConvertExcel(ByVal iBegin As Integer, ByVal iEnd As Integer) As String
        Try
            Dim contentExcelXML As String = ""
            For i As Integer = iBegin To iEnd
                contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                    "<Cell ss:Index=""" & "3""" & "><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(0, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(1, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(2, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(3, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(4, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(5, i).Value & "</Data></Cell>" & vbCrLf & _
                "</Row>"
            Next
            Return contentExcelXML
        Catch ex As Exception

        End Try
    End Function
#End Region
End Class